package cn.edu.gzhmt.order.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import cn.edu.gzhmt.goods.domain.Goods;
import cn.edu.gzhmt.order.dao.IOrderDao;
import cn.edu.gzhmt.order.domain.Order;
import cn.edu.gzhmt.order.domain.OrderItem;
import cn.edu.gzhmt.user.domain.User;
import cn.edu.gzhmt.utils.jdbc.TxQueryRunner;

/**
 * 订单模块 dao层 实现类
 * @author azhuo
 *
 */
public class OrderDaoImpl implements IOrderDao {

	private QueryRunner queryRunner = new TxQueryRunner();

	/**
	 * 添加订单
	 */
	public void addOrder(Order order) {
		System.out.println("Dao order = " + order);
		System.out.println("Dao getTotal = " + order.getTotal());
		try {
			String sql = "INSERT INTO `t_order`(`oid`,`ostate`,`total`,`time`,`uid`,`oaddress`) VALUES ( ?,?,?,?,?,?)";

			Object[] params = { order.getOid(), order.getOstate(),
					order.getTotal(), order.getTime(),
					order.getUser().getUid(), order.getOaddress() };

			queryRunner.update(sql, params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 添加订单明细（条目）
	 */
	public void addOrderItems(List<OrderItem> orderItemList) {

		try {
			String sql = "INSERT INTO `t_orderitem`(`iid`,`count`,`gid`,`subtotal`,`oid`) VALUES ( ?,?,?,?,?)";

			for (OrderItem orderItem : orderItemList) {

				Object[] params = { orderItem.getIid(), orderItem.getCount(),
						orderItem.getGoods().getGid(), orderItem.getSubtotal(),
						orderItem.getOrder().getOid() };

				queryRunner.update(sql, params);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 根据订单id查询订单
	 * 当没有订单时，可能会报空指针异常
	 */
	public Order findByOid(String oid) {
		try {
			String sql = "SELECT * FROM t_order WHERE oid=? AND odel=0";

			// 查询订单
			Order order = queryRunner.query(sql, new BeanHandler<Order>(
					Order.class), oid);

			if (order != null) {
				// 加载Order的所有条目
				loadOrderItem(order);
			}

			return order;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 加载指定订单下的所有条目
	 * @param order
	 */
	private void loadOrderItem(Order order) {

		try {
			String sql = "SELECT * FROM `t_orderitem` WHERE oid=?";

			// 查询订单
			List<OrderItem> orderItemList = queryRunner.query(sql,
					new BeanListHandler<OrderItem>(OrderItem.class),
					order.getOid());

			// 为条目关联商品
			for (OrderItem orderItem : orderItemList) {
				loadGoods(orderItem);
			}

			// 进行订单与条目的关联
			order.setOrderItemList(orderItemList);

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 为条目加载商品
	 * @param orderItem
	 */
	private void loadGoods(OrderItem orderItem) {
		try {
			String sql = "SELECT * FROM `t_goods` WHERE gid=?";

			// 查询订单
			Goods goods = queryRunner.query(sql, new BeanHandler<Goods>(
					Goods.class), orderItem.getGid());

			// 为订单关联商品
			orderItem.setGoods(goods);

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 根据用户id查询订单
	 * 按下单时间 倒序排序
	 */
	public List<Order> findByUid(String uid) {
		try {
			String sql = "SELECT * FROM `t_order` WHERE uid=? AND odel=0 ORDER BY TIME DESC";

			// 查询订单
			List<Order> orderList = queryRunner.query(sql,
					new BeanListHandler<Order>(Order.class), uid);

			// 进行关联订单条目
			for (Order order : orderList) {
				loadOrderItem(order);
			}

			// 返回
			return orderList;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 修改顶大状态
	 */
	public void updateOstate(String oid, int ostate) {
		try {

			String sql = "UPDATE `t_order` SET ostate=? WHERE oid=?";

			Object[] params = { ostate, oid };

			queryRunner.update(sql, params);

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 查询所有的订单
	 */
	public List<Order> findAll() {
		try {

			String sql = "SELECT * FROM t_order WHERE odel=0";

			List<Order> orderList = queryRunner.query(sql,
					new BeanListHandler<Order>(Order.class));

			// 加载订单的条目
			if (orderList != null) {
				for (Order order : orderList) {
					loadOrderItem(order);
					loadOrderUser(order);
				}
			}

			return orderList;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 加载订单所属的用户
	 * @param order
	 */
	private void loadOrderUser(Order order) {
		try {

			String sql = "SELECT * FROM t_user WHERE uid=?";

			User user = queryRunner.query(sql,
					new BeanHandler<User>(User.class), order.getUid());

			// 进行关联关系
			order.setUser(user);

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 多条件组合查询订单
	 */
	public List<Order> findOrder(Order order) {
		try {

			StringBuffer sqlBuf = new StringBuffer();
			List<Object> params = new ArrayList<Object>();

			//
			sqlBuf.append("SELECT * FROM `t_order` WHERE 1=1");
			// 订单id查询
			if (order.getOid() != null && order.getOid().trim().length() > 0) {
				sqlBuf.append(" AND `oid`=?");
				params.add(order.getOid());
			}

			// 订单状态查询
			if (order.getOstate() != null
					&& order.getOstate().trim().length() > 0) {
				sqlBuf.append(" AND `ostate`=?");
				params.add(order.getOstate());
			}

			// 按照时间排序
			sqlBuf.append(" ORDER BY TIME DESC");

			List<Order> orderList = queryRunner.query(sqlBuf.toString(),
					new BeanListHandler<Order>(Order.class), params.toArray());

			System.out.println("list = " + orderList);
			if (orderList != null || orderList.size() > 0) {

				// 加载订单的条目
				for (Order item : orderList) {
					loadOrderItem(item);
					loadOrderUser(item);
				}
			}

			return orderList;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 根据oid删除订单
	 * 假删除
	 */
	public void removeByOid(String oid) {
		try {

			String sql = "UPDATE `t_order` SET odel='1' WHERE oid=?";

			queryRunner.update(sql, oid);

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 特定时间查询订单
	 */
	public List<Order> statisticsOrder(String date) {
		try {
			StringBuffer sqlBuf = new StringBuffer();
			sqlBuf.append("SELECT * FROM `t_order` WHERE `odel`='0'");
			if ("today".equals(date)) {
				sqlBuf.append(" AND TO_DAYS(`time`) = TO_DAYS(NOW())");
			} else if ("yesterday".equals(date)) {
				sqlBuf.append(" AND TO_DAYS(NOW()) - TO_DAYS(`time`) <= 1");
			} else if ("day7".equals(date)) {
				sqlBuf.append(" AND DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= DATE(`time`)");
			} else if ("day30".equals(date)) {
				sqlBuf.append(" AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(`time`)");
			} else if ("month".equals(date)) {
				sqlBuf.append(" AND DATE_FORMAT(`time`, '%Y%m' ) = DATE_FORMAT( CURDATE() , '%Y%m' )");
			} else if ("lastMonth".equals(date)) {
				sqlBuf.append(" AND PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(`time`,'%Y%m')) =1");
			} else if ("year".equals(date)) {
				sqlBuf.append(" AND YEAR(`time`)=YEAR(NOW())");
			} else if ("lastYear".equals(date)) {
				sqlBuf.append(" AND YEAR(`time`)=YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR))");
			}

			List<Order> orderList = queryRunner.query(sqlBuf.toString(),
					new BeanListHandler<Order>(Order.class));

			// 加载订单的条目
			if (orderList != null) {
				for (Order order : orderList) {
					loadOrderItem(order);
					loadOrderUser(order);
				}
			}

			return orderList;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

}
